package com.jysz.certificate.web.listener;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServlet;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.cfg.Environment;
import org.hibernate.cfg.Settings;
import org.hibernate.connection.ConnectionProvider;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.mapping.Table;
import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.util.ArrayHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean;

import com.jysz.certificate.persistence.model.system.SchemaInfo;
import com.jysz.certificate.spring.SpringBeanManager;
import com.jysz.webcore.utils.FileUtils;
import com.jysz.webcore.utils.FileUtils.ISortCallback;

/**
 * 升级程序
 * */
public class DBManagerInitializer {
	private final static Logger LOG = LoggerFactory.getLogger(DBManagerInitializer.class);
	
	private static final String defaultCatalog = Environment.getProperties().getProperty(Environment.DEFAULT_CATALOG);
	private static final String defaultSchema = Environment.getProperties().getProperty(Environment.DEFAULT_SCHEMA);
	protected static final String SQL_SCRIPT_NAME_PATTERN_STR = "([0-9]*)_*.*sql";
	private static final Pattern SQL_SCRIPT_NAME_PATTERN=Pattern.compile(SQL_SCRIPT_NAME_PATTERN_STR);
	/** 数据库中没有记录 **/
	public static final int DB_NOTHING = -1;
	private static final String SQL_DB_PATH = "/WEB-INF/dbscript/";
	private static final String MENU_SQL_DB_PATH = "/WEB-INF/dbmenuscript/";
	/**
	 * schema_info表
	 */
	private static final String SCHEMA_INFO_TABLE_NAME = "sys_schema_info_";
	
	private static final String SEQUENCE_NUMBER_TABLE_NAME="sys_sequence_number_";

	private AnnotationSessionFactoryBean sessionFactory;
	
	private ServletContext context;
	
	private static class SingletonHolder {
		static final DBManagerInitializer INSTANCE = new DBManagerInitializer();
	}
	
	private DBManagerInitializer(){
	}
	
	public static DBManagerInitializer getInstance(){
		return SingletonHolder.INSTANCE;
	}
	
	
	public void initialize(HttpServlet servlet) {
		try{
			sessionFactory = (AnnotationSessionFactoryBean) SpringBeanManager.getBean("&sessionFactory");
			
			this.initSequenceNumber();
			//初始化SchemaInfo 表格
			SchemaInfo schemaInfo = this.initSchemaInfo();
			int dbVersion = schemaInfo.getDbversion();
			int menudbVersion = schemaInfo.getMenudbversion() == null ? 0 : schemaInfo.getMenudbversion();
			
			//更新菜单
			LOG.debug("------------------------开始更新菜单！----------------------------");
			int menumaxVersion = this.executeDbScript(menudbVersion, getPath(MENU_SQL_DB_PATH), true);
			
			// 把数据库的db字段更新到最大值.
			if(menumaxVersion > 0){
				LOG.debug("update database version :[" + menumaxVersion + "]");
				this.updateMenuDbMaxVersion(menumaxVersion);
			}
			
			LOG.debug("-------------------------菜单更新结束----------------------------");
			
			
			System.out.println("get database version :[" + dbVersion + "]");
			int maxVersion = 0;
			
			//更新public schema下的表
			maxVersion = this.executeDbScript(dbVersion, getPath(SQL_DB_PATH), false);
			
			LOG.debug("file version :[" + maxVersion + "]");
			
			// 把数据库的db字段更新到最大值.
			if(maxVersion > 0){
				LOG.debug("update database version :[" + maxVersion + "]");
				this.updateDbMaxVersion(maxVersion);
			}
		} catch (Exception e) {
			LOG.error("初始化失败", e);
			System.exit(0);
//			throw new WebSystemException(e);
		}
		
	}
	

	@SuppressWarnings("unchecked")
	private void updateMenuDbMaxVersion(int menumaxVersion) {
		HibernateTemplate hibernateTemplate = new HibernateTemplate(this.getSessionFactory());
		@SuppressWarnings("rawtypes")
		SchemaInfo info = (SchemaInfo)hibernateTemplate.execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException, SQLException {
						Criteria criteria = session.createCriteria(SchemaInfo.class);
						return criteria.uniqueResult();
					}
				}
		);
		
		if(info != null){
			info.setMenudbversion(menumaxVersion);
			hibernateTemplate.saveOrUpdate(info);
		}
	}

	/**
	 * 更新schema_info表
	 * 
	 * @param maxVersion 
	 */
	@SuppressWarnings("unchecked")
	public void updateDbMaxVersion(int maxVersion){
			HibernateTemplate hibernateTemplate = new HibernateTemplate(this.getSessionFactory());
			@SuppressWarnings("rawtypes")
			SchemaInfo info = (SchemaInfo)hibernateTemplate.execute(
					new HibernateCallback() {
						public Object doInHibernate(Session session) throws HibernateException, SQLException {
							Criteria criteria = session.createCriteria(SchemaInfo.class);
							return criteria.uniqueResult();
						}
					}
			);
			
			if(info != null){
				info.setDbversion(maxVersion);
				hibernateTemplate.saveOrUpdate(info);
			}
	}
	
	/*
	 * 从{@link ConnectionProvider}取得{@link Connection}
	 * @return {@ink Connection}
	 */
	private Connection getConnection(){
		Connection conn = null;
		try {
			conn = getSettings().getConnectionProvider().getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return conn;
	}
	
	/**
	 * 得到{@link SessionFactoryImplementor}
	 * @return {@link SessionFactoryImplementor}
	 */
	private SessionFactoryImplementor getSessionFactory(){
		return (SessionFactoryImplementor) sessionFactory.getObject();
	}
	
	/**
	 * 取得hibernate的{@link Settings}
	 * @return {@link Settings}
	 */
	private Settings getSettings(){
		return this.getSessionFactory().getSettings();
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private SchemaInfo initSchemaInfo() {
		Iterator<Table> it = sessionFactory.getConfiguration().getTableMappings();
		Table table = null;
		while (it.hasNext()) {
			table = it.next();
			if (!SCHEMA_INFO_TABLE_NAME.equals(table.getName())) {
				continue;
			}
				
			Connection conn = null;
			DatabaseMetadata meta = null;
			TableMetadata tableInfo = null;
			try {
				conn = getConnection();
				meta = new DatabaseMetadata(conn, getSettings().getDialect());
				tableInfo = meta.getTableMetadata(SCHEMA_INFO_TABLE_NAME, null, null, false);
				
				List<String> script = new ArrayList<String>();
				if ( tableInfo == null ) {
					System.out.println("*****************************");
					System.out.println("* CREATE Database Schema...SCHEMA_INFO_TABLE *");
					System.out.println("*****************************");
					
//					sessionFactory.createDatabaseSchema();
					if ( table.isPhysicalTable() ) {
						script.add(
								table.sqlCreateString(
										Dialect.getDialect(sessionFactory.getConfiguration().getProperties()),
										sessionFactory.getConfiguration().buildMapping(),
										defaultCatalog,
										defaultSchema
									)
							);
					}
					
					
				} else {
					System.out.println("*****************************");
					System.out.println("* UPDATE Database Schema... *");
					System.out.println("*****************************");
					Iterator<String> subiter = table.sqlAlterStrings(
							getSettings().getDialect(),
							getSessionFactory(),
							tableInfo,
							defaultCatalog,
							defaultSchema
						);
					while ( subiter.hasNext() ) {
						script.add( subiter.next() );
					}
				}
				
				Iterator<String> comments = table.sqlCommentStrings(getSettings().getDialect(),defaultCatalog, defaultSchema);
				while (comments.hasNext()) {
					script.add(comments.next());
				}
				
				final String[] sqls = ArrayHelper.toStringArray(script);
				executeSchemaScript(sqls);
			} catch (SQLException e) {
				LOG.error("升级失败，程序自动关闭............", e);
				System.exit(0);
			} finally {
				closeConnection(conn);
			}
		}
		
		//检查表中是否存在记录，如果没有，插入默认记录
		HibernateTemplate hibernateTemplate = new HibernateTemplate(this.getSessionFactory());
		SchemaInfo info = (SchemaInfo)hibernateTemplate.execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException, SQLException {
						Criteria criteria = session.createCriteria(SchemaInfo.class);
						return criteria.uniqueResult();
					}
				}
			);
		if(info == null){//如果SchemaInfo表中无记录
			//增加以条默认记录,记录的version字段保存0
			info = new SchemaInfo();
			info.setDbversion(0);
			info.setMenudbversion(0);
//			info.setDbversion(DB_NOTHING);
			hibernateTemplate.saveOrUpdate(info);
		}
				
		return info;
	}

	@SuppressWarnings("unchecked")
	private void initSequenceNumber() {
		Iterator<Table> it = sessionFactory.getConfiguration().getTableMappings();
		Table table = null;
		while (it.hasNext()) {
			table = it.next();
			if (!SEQUENCE_NUMBER_TABLE_NAME.equals(table.getName())) {
				continue;
			}
				
			Connection conn = null;
			DatabaseMetadata meta = null;
			TableMetadata tableInfo = null;
			try {
				conn = getConnection();
				meta = new DatabaseMetadata(conn, getSettings().getDialect());
				tableInfo = meta.getTableMetadata(SEQUENCE_NUMBER_TABLE_NAME, null, null, false);
				
				List<String> script = new ArrayList<String>();
				if ( tableInfo == null ) {
					System.out.println("*****************************");
					System.out.println("* CREATE Database Schema...SEQUENCE_NUMBER_TABLE *");
					System.out.println("*****************************");
					
//					sessionFactory.createDatabaseSchema();
					if ( table.isPhysicalTable() ) {
						script.add(
								table.sqlCreateString(
										Dialect.getDialect(sessionFactory.getConfiguration().getProperties()),
										sessionFactory.getConfiguration().buildMapping(),
										defaultCatalog,
										defaultSchema
									)
							);
					}
					
					
				} else {
					System.out.println("*****************************");
					System.out.println("* UPDATE Database Schema... *");
					System.out.println("*****************************");
					Iterator<String> subiter = table.sqlAlterStrings(
							getSettings().getDialect(),
							getSessionFactory(),
							tableInfo,
							defaultCatalog,
							defaultSchema
						);
					while ( subiter.hasNext() ) {
						script.add( subiter.next() );
					}
				}
				
				Iterator<String> comments = table.sqlCommentStrings(getSettings().getDialect(), defaultCatalog, defaultSchema);
				while (comments.hasNext()) {
					script.add(comments.next());
				}
				
				final String[] sqls = ArrayHelper.toStringArray(script);
				executeSchemaScript(sqls);
			} catch (SQLException e) {
				LOG.error("升级失败，程序自动关闭............", e);
				System.exit(0);
			} finally {
				closeConnection(conn);
			}
		}
		
	
				
	}
	
	/**
	 * 执行sql语句,同时更新数据库的最大版本号
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void executeSchemaScript(String... sqls) throws SQLException {
		Connection conn = null;
		try {
			conn = this.getSettings().getConnectionProvider().getConnection();
			executeSchemaScript(conn, sqls);//执行ddl
			for(String sql:sqls){
				LOG.debug("execute:" + sql);
			}
		} catch (SQLException e) {
			LOG.error("升级失败，程序自动关闭............", e);
			throw e;
//			System.exit(0);
		} finally{
			this.closeConnection(conn);
		}
	}
	
	/**
	 * Execute the given schema SQL on the given JDBC Statement.
	 * <p>Note that the default implementation will log unsuccessful statements
	 * and continue to execute. Override this method to treat failures differently.
	 * @param stmt the JDBC Statement to execute the SQL on
	 * @param sql the SQL statement to execute
	 * @throws SQLException if thrown by JDBC methods (and considered fatal)
	 */
	protected void executeSchemaStatement(Statement stmt, String sql) throws SQLException {
		if (LOG.isDebugEnabled()) {
			LOG.debug("Executing schema statement: " + sql);
		}
		try {
			stmt.executeUpdate(sql);
		}catch (SQLException ex) {
			if (LOG.isWarnEnabled()) {
				LOG.warn("Unsuccessful schema statement: " + sql, ex);
			}
			throw ex;
		}
	}
	
	protected void executeSchemaScript(Connection con, String[] sql) throws SQLException {
		if (sql != null && sql.length > 0) {
//			boolean oldAutoCommit = con.getAutoCommit();
//			if (!oldAutoCommit) {
//				con.setAutoCommit(true);
//			}
			con.setAutoCommit(false);
			try {
				Statement stmt = con.createStatement();
				
				try {
					for (String sqlStmt : sql) {
						stmt.addBatch(sqlStmt);
//						executeSchemaStatement(stmt, sqlStmt);
					}
					stmt.executeBatch();
					con.commit();
				}catch(Exception e){
					con.rollback();
					throw new SQLException(e);
				}finally {
					JdbcUtils.closeStatement(stmt);
				}
			}
			finally {
//				if (!oldAutoCommit) {
//					con.setAutoCommit(false);
//				}
			}
		}
	}
	
	/**
	 * 关闭给定的{@link Connection}
	 * <p>所有使用的{@link Connection}都来自{@link ConnectionProvider},因此调用{@link ConnectionProvider}的closeConnection方法关闭
	 * @param conn
	 */
	private void closeConnection(Connection conn){
		if(conn != null){
			try {
				this.getSettings().getConnectionProvider().closeConnection(conn);
			} catch (SQLException e) {
				LOG.debug("when close connection get the Exception:"+e);
			}
		}
	}
	/**
	 * 根据DB中SchemaInfo表中的Version字段的内容，判断应该执行的groovy脚本
	 * @param scriptType 执行脚本类型:'INDEX':执行更新index的脚本;'DB':执行更新数据库的脚本
	 * @param dbVersion 数据库的版本
	 * @param dbPath 数据库脚本的路径
	 * @param isMenuExec 
	 */
	protected int executeDbScript(int dbVersion, String dbPath, boolean isMenuExec) throws Exception{
		
		System.out.println("Sql script  path:[" + dbPath + "]");
		
		File[] files = FileUtils.dir(dbPath + File.separator + SQL_SCRIPT_NAME_PATTERN_STR).sort(new ISortCallback(){
			public int getSortData(File file) {
				Matcher matcher=SQL_SCRIPT_NAME_PATTERN.matcher(file.getName());
				if(matcher.find()){
					return Integer.parseInt(matcher.group(1));
				}
				return -1;
			}
			
		}).getFiles();
		
		//是否存在脚本文件
		if(files == null || files.length == 0){
			return 0;
		}
		
		//得到文件中的最大值
		int fileMaxVersion = getFileVersion(files[files.length - 1].getName());
		
		//初次建库或者数据库版本和文件版本一致
		if(dbVersion == DB_NOTHING || dbVersion >= fileMaxVersion){
			return fileMaxVersion;
		}
		
		int fileVersion = 0;
		List<String> script = null;
		for(File file:files){
			fileVersion = getFileVersion(file.getName());
			System.out.println("load sql script file :[" + file.getAbsolutePath() + "]");
			
			if(fileVersion == 37){
				System.out.println("1");
			}
			
			//仅仅对大于dbVersion进行执行
			if(fileVersion > dbVersion){
				script = readUpdateSQL(file);
				final String[] sqls = ArrayHelper.toStringArray(script);
				try{
					executeSchemaScript(sqls);
				}catch(Exception e){
					LOG.error("更新失败，更新到" + fileVersion, e);
					System.exit(0);
				}
				if(isMenuExec){
					this.updateMenuDbMaxVersion(fileVersion);
				}else{
					this.updateDbMaxVersion(fileVersion);
				}
				
			}
		}
		
		return fileMaxVersion;
	}
	
	/**
	 * 读取升级文件
	 * */
	private List<String> readUpdateSQL(File file) throws Exception {
		
		BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "utf-8"));
		String temp = "",
			   sqlCommand = "";
		List<String> sqlCommands = new ArrayList<String>();
		while ((temp = reader.readLine()) != null) {

			// 忽略空行及sql注释
			if(StringUtils.isBlank(temp) || temp.startsWith("--")){
				continue;
			}
			
				// 处理多行sql
				sqlCommand = sqlCommand + " " + temp;
				if(temp.trim().endsWith(";")){
					sqlCommands.add(sqlCommand);
					sqlCommand = "";
				}
		}
		reader.close();
		
		return sqlCommands;
	}
	
	/**
	 * 通过给定的文件名来得到文件的版本号.
	 * @param filename Groovy文件
	 * @return 文件的版本号
	 */
	private int getFileVersion(String filename){
		Matcher matcher = SQL_SCRIPT_NAME_PATTERN.matcher(filename);
		
		if(matcher.find()){
			return Integer.parseInt(matcher.group(1));
		}
		return -1;
	}
	
	public ServletContext getContext() {
		return context;
	}

	public void setContext(ServletContext context) {
		this.context = context;
	}
	
	protected String getPath(String path) {
		String realPath = context.getRealPath(path);
		if (realPath == null) throw new RuntimeException("Could not find resource " + path);
		// remove any trailing slash
		if (realPath.endsWith("/")) {
			realPath = realPath.substring(0, realPath.length() - 1);
		}
		return realPath;
	}
	
}
